﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
using System.Data.Common;
using System.Data;
using System.Reflection;

namespace Wjtour.Common
{
    public class DbHelper
    {
        private Database _database;
        private DbConnection _connection;
        private DbTransaction _transaction;

        public DbHelper()
        {
            _database = DatabaseFactory.CreateDatabase();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name">configuration key for database service</param>
        public DbHelper(string name)
        {
            if (string.IsNullOrEmpty(name))
                _database = DatabaseFactory.CreateDatabase();
            else
                _database = DatabaseFactory.CreateDatabase(name);
        }

        /// <summary>
        /// 当前数据库实例
        /// </summary>
        public Database CurrentInstance
        {
            get
            {
                return _database;
            }
        }

        /// <summary>
        /// 当前事务
        /// </summary>
        public DbTransaction CurrentTran
        {
            get { return _transaction; }
        }
        /// <summary>
        /// 获取一个连接
        /// </summary>
        /// <returns></returns>
        public DbConnection OpenConnection()
        {
            if (_connection == null)
            {
                _connection = _database.CreateConnection();
                _connection.Open();
            }
            if (_connection.State != ConnectionState.Open)
            {
                _connection.Open();
            }
            return _connection;
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        public void CloseConnection()
        {
            if (_connection != null)
            {
                _connection.Close();
                _connection.Dispose();
                _connection = null;
            }
        }
        /// <summary>
        /// 开始数据库事务，这个方法里面会先开启openConnection
        /// </summary>
        public void BeginTrans()
        {
            OpenConnection();
            _transaction = _connection.BeginTransaction();
        }


        public DbCommand CreateCommand()
        {
            OpenConnection();
            DbCommand cmd = _connection.CreateCommand();
            return cmd;
        }

        /// <summary>
        /// 提交事务，提交后会自动关闭连接
        /// </summary>
        public void CommintTrans()
        {
            _transaction.Commit();
            if (_transaction != null)
            {
                _transaction.Dispose();
                _transaction = null;
            }
            if (_connection != null)
            {
                _connection.Close();
                _connection.Dispose();
                _connection = null;
            }
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTrans()
        {
            _transaction.Rollback();

            if (_transaction != null)
            {
                _transaction.Dispose();
                _transaction = null;
            }
            if (_connection != null)
            {
                _connection.Close();
                _connection.Dispose();
                _connection = null;
            }
        }

        public void Dispose()
        {
            if (_transaction != null)
            {
                _transaction.Dispose();
                _transaction = null;
            }
            if (_connection != null)
            {
                _connection.Close();
                _connection.Dispose();
                _connection = null;
            }
        }

        #region ExecuteNonQuery
        public int ExecuteNonQuery(string sql)
        {
            DbCommand cmd = _database.GetSqlStringCommand(sql);
            return _database.ExecuteNonQuery(cmd);
        }

        public int ExecuteNonQuery(DbCommand cmd)
        {
            if (_transaction != null)
                return _database.ExecuteNonQuery(cmd, _transaction);
            else
                return _database.ExecuteNonQuery(cmd);
        }

        public int ExecuteNonQuery(string storeProcedureName, params object[] parameterValues)
        {
            DbCommand cmd = _database.GetStoredProcCommand(storeProcedureName, parameterValues);
            if (_transaction != null)
                return _database.ExecuteNonQuery(cmd, _transaction);
            else
                return _database.ExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 以参数的形式执行sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, List<DbParameter> parameters)
        {
            DbCommand cmd = _database.GetSqlStringCommand(sql);
            cmd.Parameters.AddRange(parameters.ToArray());
            if (_transaction != null)
                return _database.ExecuteNonQuery(cmd, _transaction);
            else
                return _database.ExecuteNonQuery(cmd);
        }
        #endregion

        /// <summary>
        /// 添加输入参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="name"></param>
        /// <param name="dbtype"></param>
        /// <param name="value"></param>
        public void AddInParameter(DbCommand cmd, string name, DbType dbtype, object value)
        {
            _database.AddInParameter(cmd, name, dbtype, value);
        }

        /// <summary>
        /// 添加输出参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="dbtype"></param>
        /// <param name="name"></param>
        public void AddOutParameter(DbCommand cmd, DbType dbtype, string name)
        {
            _database.AddOutParameter(cmd, name, dbtype, int.MaxValue);
        }

        #region ExecuteScalar
        /// <summary>
        /// 根据直接拼接的sql语句返回第一行一个字段,或者用带参数的形式，在后面传入
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public object ExecuteScalar(DbCommand cmd)
        {
            if (_transaction != null)
                return _database.ExecuteScalar(cmd, _transaction);
            else
                return _database.ExecuteScalar(cmd);
        }

        /// <summary>
        /// 根据存储过程返回第一行一个字段
        /// </summary>
        /// <param name="storeProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public object ExecuteScalarProcedure(string storeProcedureName, params object[] parameterValues)
        {
            if (_transaction != null)
                return _database.ExecuteScalar(_transaction, storeProcedureName, parameterValues);
            else
                return _database.ExecuteScalar(storeProcedureName, parameterValues);
        }
        #endregion

        #region GetObjects
        /// <summary>
        /// 带参数的查询，类型 "select * from table1 where field1 =@field1 ",
        /// 这时cmd中要将参数赋值好。一般调用次序
        /// 1.DbHelper.CreateCommand
        /// 2.DbHelper.AddInParameter
        /// 3.DbHelper.GetObjects
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmd"></param>
        /// <param name="rowMapper"></param>
        /// <returns></returns>
        public IEnumerable<T> GetObjects<T>(DbCommand cmd, IRowMapper<T> rowMapper = null)
            where T : new()
        {
            if (rowMapper == null)
                rowMapper = MapBuilder<T>.BuildAllProperties();

            IDataReader reader = _database.ExecuteReader(cmd);
            using (reader)
            {
                while (reader.Read())
                {
                    yield return rowMapper.MapRow(reader);
                }
            }
        }

        /// <summary>
        /// 存储过程返回对象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public IEnumerable<T> GetObjects<T>(string storeProcedureName, params object[] parameterValues)
            where T : new()
        {
            DbCommand cmd = _database.GetStoredProcCommand(storeProcedureName,parameterValues);
            IRowMapper<T> rowMapper = MapBuilder<T>.BuildAllProperties();
            IDataReader reader = _database.ExecuteReader(cmd);
            using (reader)
            {
                while (reader.Read())
                {
                    yield return rowMapper.MapRow(reader);
                }
            }
        }

        /// <summary>
        /// 普通方式查询,参数集成到sql语句中，类型 "select * from table1 where field1 ='abc' "
        /// 调用次序，拼接好sql直接传入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="rowMapper"></param>
        /// <returns></returns>
        public IEnumerable<T> GetObjects<T>(string sql, IRowMapper<T> rowMapper = null)
            where T : new()
        {
            if (rowMapper == null)
                rowMapper = MapBuilder<T>.BuildAllProperties();
            return _database.ExecuteSqlStringAccessor(sql, rowMapper);
        }
        #endregion
    }
}
